﻿using SQLite;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Wimm.Common.Entities;
using Wimm.Common.Entities.Utils;
using Wimm.Common.Interfaces.Repositories;
using Wimm.Common.Interfaces.Utils;
using Wimm.DAL;
using Wimm.DAL.Repositories;

namespace WIMM.DAL.Repositories
{
    public class IncomeRepository: SqlLightBaseRepository, IIncomeRepository
    {
        public IncomeRepository(SQLiteAsyncConnection connection)
            :base(connection)
        {

        }

        public IncomeRepository()
            :base()
        {

        }

        public async Task<IEnumerable<Wimm.Common.Entities.IncomeSource>> LoadAllSources()
        {
            return await _connection.Table<IncomeSource>().ToListAsync();
        }

        public async Task<IPagedResult<Income>> LoadPaged(IPagedRequest<Income> request)
        {
            IEnumerable<Income> incomes = await _connection.Table<Income>().Where(request.Where)
                .Skip(request.PageNumber * request.PageSize).Take(request.PageSize).ToListAsync();
            int rowCount = await _connection.Table<Income>().Where(request.Where).CountAsync();

            return new GenericPagedResult<Income>(request.PageNumber, request.PageSize, (rowCount / request.PageSize) + 1, incomes);
        }

        public override async Task<bool> SeedData()
        {
            bool seedIsNeeded = await base.SeedData();

            if (seedIsNeeded)
            {
                await _connection.CreateTablesAsync<IncomeSource, Income>();

                var resa = await _connection.CreateTablesAsync<IncomeSource, Income>();

                Icon bonuseIcon = new Icon("bonuse.png");
                Icon bussinessIncameIcon = new Icon("businessIncame.png");
                Icon dividendsIcon = new Icon("dividends.png");
                Icon incammeIcon = new Icon("incame.png");
                Icon investmentIcon = new Icon("investment.png");
                Icon otherIncameIcon = new Icon("otherIncame.png");
                Icon owertimeIcon = new Icon("owertime.png");
                Icon pensionIcon = new Icon("pension.png");
                Icon salaryIcon = new Icon("salary.png");
                Icon backIcon = new Icon("Back_Income.png");
                await _connection.InsertAllAsync(new Icon[] { bonuseIcon, bussinessIncameIcon, dividendsIcon, incammeIcon,
                    investmentIcon, otherIncameIcon, owertimeIcon, pensionIcon, salaryIcon, backIcon});

                IncomeSource PersonalIncomeParent = new IncomeSource("Personal income", incammeIcon.Id ,"IncomePersonal");
                IncomeSource InvestmentParent = new IncomeSource("Investment income", investmentIcon.Id, "IncomeInvestment");
                IncomeSource Other = new IncomeSource("Other income", otherIncameIcon.Id, "IncomeOther");
                await _connection.InsertAllAsync(new IncomeSource[] { PersonalIncomeParent, InvestmentParent, Other });

                IncomeSource bonuses = new IncomeSource("Bonuses", bonuseIcon.Id, "IncomePersonalBonuses", PersonalIncomeParent.Id);
                IncomeSource business = new IncomeSource("Business owner income", bussinessIncameIcon.Id, "IncomePersonalBusiness", PersonalIncomeParent.Id);
                IncomeSource salary = new IncomeSource("Salary", salaryIcon.Id, "IncomePersonalSalary", PersonalIncomeParent.Id);
                IncomeSource pension = new IncomeSource("Pension", pensionIcon.Id, "IncomePersonalPension", PersonalIncomeParent.Id);
                IncomeSource overtime = new IncomeSource("Overtime", owertimeIcon.Id, "IncomePersonalOvertime", PersonalIncomeParent.Id);
                await _connection.InsertAllAsync(new IncomeSource[] { bonuses, business, salary, pension, overtime });

                IncomeSource dividends = new IncomeSource("Dividends", dividendsIcon.Id, "IncomeInvestmentDividends", InvestmentParent.Id);
                await _connection.InsertAllAsync(new IncomeSource[] { dividends });
            }
            return seedIsNeeded;
        }

        public async Task<List<IncomeSource>> LoadAllSources(int? parentId)
        {
            return await _connection.Table<IncomeSource>().Where(c => c.ParentId == parentId).ToListAsync();
        }

        public async Task<Income> InsertIncome(Income newIncome)
        {
            int count = await _connection.InsertAsync(newIncome);

            return newIncome;
        }

        public async Task AddIncome(Income income)
        {
            await _connection.InsertAsync(income);
        }

        public async Task<IncomeSource> LoadSourceById(int id)
        {
            return await _connection.Table<IncomeSource>().Where(x => x.Id == id).FirstOrDefaultAsync();
        }

        public async Task<Income> EditIncome(Income income)
        {
            int count = await _connection.UpdateAsync(income);

            return income;
        }

        public async Task<IEnumerable<IncomeSource>> LoadCategories()
        {
            return await _connection.QueryAsync<IncomeSource>("select distinct [is].* from incomeSource [is]");
        }

        public async Task<IReadOnlyCollection<CategoryGroup>> LoadGrouped(DateTime? fromFilter, DateTime? toFilter, int userId)
        {
            String sql = @"SELECT IncomeSourceId 'CategoryId',SUM(i.Amount) 'Amount' FROM Income i WHERE UserId = ? ";
            List<object> sqlParams = new List<object>();
            sqlParams.Add(userId);

            if (fromFilter.HasValue)
            {
                sql += " AND [Time] >= ?";
                sqlParams.Add(fromFilter.Value.Ticks);
            }

            if (toFilter.HasValue)
            {
                sql += " AND [Time] <= ?";
                sqlParams.Add(toFilter.Value.Ticks);
            }

            sql += " GROUP BY i.IncomeSourceId  ";

            List<CategoryGroup> queryRes = await _connection.QueryAsync<CategoryGroup>(sql, sqlParams.ToArray());

            return queryRes;
        }

        public async Task<IncomeSource> LoadCategoryById(int categoryId)
        {
            return await _connection.Table<IncomeSource>().Where(c => c.Id == categoryId).FirstOrDefaultAsync();
        }

        public async Task<IncomeSource> LoadRootCategory(int categoryId)
        {
            String sql = @"SELECT * from IncomeSource where Id IN (
                WITH RECURSIVE
                parent_of(id,parentid,name) AS (
                     SELECT Id,ParentId,Name FROM IncomeSource WHERE Id = ?
                     UNION 
                     SELECT ec.Id,ec.parentid, ec.name FROM IncomeSource ec, parent_of
	                WHERE ec.Id = parent_of.parentid
                  )
                  SELECT id FROM parent_of WHERE parentid IS NULL
                )";

            List<IncomeSource> rootCategory = await _connection.QueryAsync<IncomeSource>(sql, categoryId);

            return rootCategory.First();
        }

        public async Task<DateTime> GetLimitDate(bool isMaxDate)
        {
            string sql;
            if (isMaxDate)
            {
                sql = "select * from Income where time = ( select max(time) from Income )";
            }
            else
            {
                sql = "select * from Income where time = ( select min(time) from Income )";
            }
            var expense = (await _connection.QueryAsync<Income>(sql)).FirstOrDefault();
            return (expense != null) ? new DateTime(expense.Time) : DateTime.Now;
        }
    }
}
